from random import randint

from sqlalchemy import Column, Integer, String, ForeignKey, func
from sqlalchemy.orm import relationship, backref

from db_util import Base, Session,engine


class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    age = Column(Integer)

    def __repr__(self):
        return f'<User: id={self.id} name={self.name} age={self.age}>'


class News(Base):
    __tablename__ = 't_news'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(32), nullable=False)
    content = Column(String(32), nullable=False)
    read_count = Column(Integer)

    uid = Column(Integer)

    # uid = Column(Integer,ForeignKey('t_user.id'))
    # user = relationship('User',backref=backref('newss'))

    def __repr__(self):
        return f'<News: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'


def create_data():
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    with Session() as ses:
        for i in range(2):
            user = User(name=f'name{i}', age=randint(6, 30))
            ses.add(user)
        for i in range(10):
            news = News(title=f'title{i}', content=f'info{i}', read_count=randint(0, 1000), uid=randint(1, 2))
            ses.add(news)
        ses.commit()


def query_join():
    # 找到所有的用户，新闻的数量，按照发表的新闻数量进行排序
    '''
     SELECT u.name,count(n.id)
     from t_user u
     join t_news n
     on u.id = n.uid
     group by u.id
     order by count(n.id);
     '''
    with Session() as ses:
        # join里面放对象类名时，默认会以外键做为关联条件
        rs = ses.query(User.name, func.count(News.id)).join(News).group_by(User.id).order_by(func.count(News.id)).all()
        print(rs)


def query_join_by_self():
    # 找到所有的用户，新闻的数量，按照发表的新闻数量进行排序
    '''
     SELECT u.name,count(n.id)
     from t_user u
     join t_news n
     on u.id = n.uid
     group by u.id
     order by count(n.id);
     '''
    with Session() as ses:
        # join里面放对象类名时，默认会以外键做为关联条件
        rs = ses.query(User.name, func.count(News.id)).join(News, News.uid == User.id).group_by(User.id).order_by(
            func.count(News.id)).all()
        print(rs)


if __name__ == '__main__':
    create_data()
    # query_join()
    # query_join_by_self()


